Ok folks...

Some of might remember that a couple of days back I was talking about 'how to make a nice hand-out of all music stored on the Empeg'. At the same time I was already playing around with VBA for MS-Office, and this weekend I've completed a macro for this. Its still very beta stuff, so keep me informed for bugs or extra features required.
Let me know how you think about this toy!

What is it
It's a MS-Word 6.0 (and up) document including a macro which uses the Empeg database file (in CSV-format) as input for generating a catalogue. This database file can be exported from Emplode or jEmpode (be sure selecting CSV-format).
On request the macro generates:
- Table of Contents
- Overview of albums with their title - artist
- Overview of artists with their songs

What does it need
- MS-Word 95/97 (6.0 and up)
- Excel (since its interfacing to Excel)
- A printer

How to use
Unzip the EmpegMusicCatalogue.zip and open the document inside Word. Next execute the macro by ALT+F8 and play the 'StartGeneratingCatalogue'-macro.

What do I want
Nothing
Just deliver me an e-mail if you like it with bug reports and recommendations.

Another request from me
Who can store this on www.riocar.org? I do not have a server to put my code there.

Sorry, but the download does not work...Maybe your file is to big!

bye

Seems that the attachment gives problems.
The size seems be the problem (313kB, while 200k is max), so stay in touch while I'm trying to fix the problem.

MS-Word seems to make itself crazy regarding picture handling and blowed the filesize. I've deleted and re-inserted the pictures and the size of the ZIP has now shrunk from 313kB to 91kB.

Give it a try..

It was indeed the size.
Have fixed it now by new attachement in a reply

I keep getting this error "The macros in this project are disabled. Please refer to the online help or documentation of the host appliucations to determine how to enable macros" any ideas?

nevermind figured it out, you must have excel running int he background

new situation, im getting an "Run-time erroe '1004;: Methond 'Range' of object '_Global' failed" any ideas?

Ditto.



Tom

ya kinda of a bummmer, hopefully u can get it fixed soon it looks really great from what i see, i could u a nice soild catalog thats for the effort you are making

Should we take up a collection for poor college student Justin, who apparently cannot afford the shift-key and punctuation upgrades for his keyboard?

Oh man...
I simply hate that VBA-stuff. They call it sometimes the programming language for dummies, but I prefer bare C/C++ programming.

About the problems you ran into:
I've executed this macro at my home place (Office97) and work (Office 97 + 2000), both running without problems. The excel problem sounds strange to me because windows should automatically shoots the excel object.

The other one (runtime error) seems to be related to the excel.

Question
What office package you are using

Give me a moment to investigate it. Unfortunately this will be this afternoon since I've a couple of customers looking over my shoulders at work.

Thanks anyway,

EiSl

Bitt, please take note of the time i wrote that post. Its clear that it was very early in the morning/late in the evening, and i was still a little bit drunk. Give me a break.

Im running The Office Xp Suite, that might have something to do with it. I updated all my VB Runtimes as well still no dice.

It only runs for me only with Excel closed prior to running (WinXP & Office XP). I've noticed that at times, Excel does not show on the taskbar but is present in task manager (not your problem but may explain why others are having problems and getting the 1004 error).

However, something isn't working properly for me......

The "Albums" page empty and on the "Artists" page I just get a single column of numbers (one per line, aligned to LH margin) which in fact are the track lengths (they also appear in column D "length" in Excel).

Hello all,

Seems that different people all have the same problem. I will suggest not download this until further notice.
I the meantime I will dive into the trouble.
To give you all an impression what this macro actually produces I've attached an example.

Sorry for this inconvenience, for me its of course a bad start...

You will hear soon something...

EiSl

Hope you get it fixed soon. I tried it (didn't work for me either on W2K), but the result looks promising!

Frank

I think any start is infinitely better than not trying at all. I'm impressed by what you've done so far. It's more than I could do.

Hello folks,

I've made some modifications. The range errors was because of different layouts of (j)Emplode CSV-files. I didn't checked that (ough) but I thought it was the same. Well... Emplode exports 30 columns, jEmplode only 17. So a small redesign is made.
I've also made a option making the interfacing to Excel visible. You can checkmark this. As a last test I've build myself a new catalogue using the CSV-files of both (j)Emplode -> works fine (for me )

OK lets try this one. Keep me in touch for other errors. If you get an error, then also try the other CSV-file. At the end I can provide someone the password to unprotect the macro so debugging is easier.

Cheers,

EiSl

I havent been able to get past the "Macros in this project are disabled..." error... I have excel open, i've tried with both files in excel, and excel closed. OfficeXP

edit: ok, I needed to reopen your file, but upon doing so, I still get a "could not load object because it is not available on this machine", VB?? Then I hit run, and get the error again, then when I get the screen to load my database, I get the visual basic error: "Compile error in hidden module: IntroDlg"

same message here...I am using also Office XP!

I have Office 2000 and I am getting the following attached error.

"System Error &H8000FFFF (- 2147418113).Catastrophic failure "

Where does the CVS file need to reside for the macro to find it?


This sounds like it is a great thing. I cannot wait use it.

looks like i got it working, its seems to be im getting the results now THANKS!

ok i got most of the way thru then i got a Run-Time Error '13' type mismatch. This ended on page 73, also no table of contents, and the 2 col check mark is greyed out on XP, im just gunna get a version of office 97 to run it, and stop complaing but i do love this it super cool. at least we are getting somewhere

also i can go ahead and host it for you on riocar.org

I don't think this is an error. Go to Tools click on macro, than security. Lower your security option to allow this macro to run.

It works for me. Thank You! Is it possible to sort Artist - Album - Title? Do you expect to add the Pin Number? Even if you do nothing else its very nice. Thank You for your hard work.

So did anyone figure out how to get by the "Could not load an object because it is not available on this machine." error in XP?

- Jon

That works MUCH better

It does however throw up a type mismatch (error 13) when it encounters a missing Artist name (column M in Excel).
Excel represents this missing field as a #NAME? - I think a fairly simple "on error do <something> resume next" in VBA would probably fix this adequately.

yeep, now it works! Thanks for your help!

Yippie it works now ;-)

Feel free to do so (hosting).
I was asking if someone wanted to do it for me since I do not have any webspace to store the download. Then it was mentioned that I could take the forum link as download source.

But I prefer a separate place, so if you want to host it I would be (and others) very happy!

With regards,

Eize Slange
(Empeg, Mk2A, Blue,20+40GB playing in 206XS around Utrecht, NL)

A comment about the two-column mode: that a future something because I have to do it as a post-processing action. Initial setting to two-column and than add the stuff didn't work.

About that run-time error: any idea where this comes from? My catalogue is around 120 pages in size, so that shouldn't be the problem.

The macro runs fine on Office 97+2000 running on NT4 or Win2k. But the problem with basic is that some errors are realy hard to find. Maybe you can send me a zipped version of you CSV-file, so I can check if I can reproduce the error. If you are a programmer yourself I maybe give you the password to unprotect the macro (which you can actually crack using one of that many tools).

Cheers,
Eize Slange
([email protected])

Hello MicMan,

Office 2000 should be the problem since I;ve tested it on it on a Win2k machine. Mentioned error sounds as a realy rocky horror show. I've also tested it on a NT4-machine running Office97.

The CSV-file can be stored on any place on your harddisk. Is that what you mean? You just select the file by browsing to it (pressing that button on the right).

Keep it touch. Maybe its a simple thing I can fix quite fast.

Cheers,

Eize Slange

I'm not sure, because some of you are using Office XP. It can possible be one of next issues:
- Macro security settings (lower the security)
- Excel is not installed, so it cannot find the excel object

Keep me informed, possible provide me extra information so I can check if it is a bug in the maco. VBA.... I looks so simple bug you get sometimes realy strange errors. Unfortunately I do not have Office XP installed yet (I have access to Office 97/2000 on NT4/Win2k/XP machines).

Keep me in touch,

Eize Slange

Lower your macro security. This should fix that part. In any case: if something was already opened, close everything before retrying.

Cheers,
Eize Slange

What version are you using?
I've just submitted 1.02 beta 2, because of a BIG annoying bug (check the threads). The previous macro did not have a version line in the window caption. About Excel not visible: that correct, since I open only the object and do not tell it to become visible. In next version you can checkmark this for e.g. debugging.

Cheers,
EiSl

Ah... now we coming at wish-list stuff

What you are asking is actually peanuts to do. PINs were useless for me at the moment of writing. About your sorting question: do you mean to sort first on Artist next on album then title for the same contents? Sounds a bit strange layout for me, but your wish my command

You are free to make (and collect) a list of wishes for this maco. Next week I'm on holiday (driving through Europe) so I'm not able to work on it. It was for my holiday I mado a ugly macro myself, figuring out it could be very usefull for others too. Then I started over again with a GUI interface. Till my holiday I'm trying to make bugfixes. New features are for after the holiday.

With regards,
Eize Slange

I think that would indeed an good thing to do before I'm on holiday. I will try to include the 'on error' stuff. Watch for coming releases. Empty artist name ?!?! how you fill in your tag's?

Cheers,
Eize

Hm. Well, I tried everything to get it working with Office XP. Security settings don't seem to help, nor does opening/closing Excel at different times...

If anyone is able to figure something out, of course, please post it here...

- Thanx
- Jon

Try this: After changing the security setting, close WinWord, start it again and then execute the macro with ALT-F8!

bye

Yup. Tried that a few times. I still am getting an error that says "Could not load an object because it is not available on this machine". I clock on ok, and then the script executes. I can see the catalogue generator dialog box, but cannot put anything in the "Empeg music DB: area. If I click browse, I get a compile error, and the program stops executing...

- Jon

About "Could not load an object because it is not available on this machine": do you have Excel installed?

Can you also give me the error message (maybe screen-shot) of the compile error when you press the 'Browse' button to select the CSV-file?
Extra info: the line where the path is displayed is marked 'not editable' since I want to prevent miss-typed path+filenames so forcing the use of the browse button.
It is also possible that the object/compiler error pop-up because you mising some OCX'en (runtime libraries for VB).

Editted
I've just checked the code. The object-error you get is because the fact that you missing some files on your machine (or are invalid for this macro). Since you've also problems with the browse-button, I think it has to do with the Common Dialog control (comdlg32.ocx), which includes the file-open dialog. The browse-button launches this object and that's where you get the compile error.
Can you check what version of comdlg32.ocx you have? It can be found in the windows directory.

Good luck and I'm awaiting your input,

Eize

Hm. I don't have that file at all on my PC... How do I get the run time VB stuff? Can I download that from Msft?

- Jon

Maybe this helps:

http://www.webattack.com/help/missingfiles.html

bye

I would like to see it with the Table of Contents pretty much as it is now, a alphabetical listing of Artists. The difference would be that instead of just listing all of the songs by the Artist, they would be listed under the Artist heading by Album. I think this would make it much easier to know where to go on the Empeg in order to find a specific song. Heres an example of what I mean:

Pink Floyd

__Animals

____Pigs on the Wing (Part 1)
____Dogs
____Pigs
____Sheep
____Pigs on the Wing (Part 2)

__Wish You Were Here

____Shine on You Crazy Diamond (Part 1-5)
____Welcome to the Machine
____Have a Cigar
____Wish You Were Here
____Shine on You Crazy Diamond (Part 6 - 9)


Scopions

__Big City Nights

____Big City Nights
____Animal Magnetism
____Send Me an Angel
____Rock You Like a Hurricane


And so on and so forth

The underscore was added just as a place holder as a space at the front of a line is lost with this editor.

This way you know which album the song is on and by which Artist.

Thanks Again for you efforts.

Ok,
When I set the Macro to Step Into instead of Run, I get the same error as before then the following error:

Microsoft Visual Basic
Compile error in hidden module : CatalogueGeneration

I am having no luck at this...

OK its noted.

BTW> When I tagged all my mp3's I have added the artist name in front of the album name (like: "Dire Straits - Live on BBC"), so I can copy-past everything in one move from the albums view to a playlist.

How you cope with (or how do you this macro copes with)albums that contain a collection (lets say a: "Top Hits of 2002" with different artists?
For songs that do not have an album tag (but do have artist tag), I will put them under artist - <no album> - 'the songs'. Agree?

I think it's quite simple to do, so I will make some choice possibilities in the layout to use when I'm back from holiday.

With regards,

Eize

Really weird...
I still think this is something regarding missing or corrupted files/components, mention to earlier error "System Error &H8000FFFF (- 2147418113).Catastrophic failure"

Can you e-mail me directly to "eslange@bigfoot"? Then I will provide you the password so you can really step into the code and check where it goes wrong.
I suggest you try this document/macro also on another machine (just zip the CSV-file... it's plain text so the size decrease significant). As from wednesday I'm on holiday, so be fast

Good luck & I'm awaiting your mail,

Eize

ok cool i got it working 100% on XP, i look thru my database i had one song that got messed up in the tagging and had the #name? in there. so now becuase of my nature is request time.

1. Anyway to make it just do Artist - Album for the table of contents insted of 2 section?

2. When Listed can you make it do Track Number - Artist - Song Title.

3. Maybe add to the left or right to the album title a spot to place an image of the album name, that will otherwise not be visable if nothing it put there.

thats about it for now, look forward to a .doc file to clear up the formatting im looking for, maybe if you can release the password to the forum some other people can take a stap at solving errors, and add features to it. Thanks for a great creation.

Ps. my catalog is 237 Pages, and i still need to add more music to my player

Ough... with that many pages you realy might prefer two-column usage. How much time did processing take? And more to come... oh man... how many gig HDD your Empeg have?
In the future you can expect next question: "What does that bible do in your car?" "Bible? No.. it's my music catalogue"

Question:
I do not understand your first comment (1. Anyway...section?)

The second comment: are you still talking about the table of contents stuff? You know that the TOC is generated automatically based on the formatting style of the text, so requirements in TOC are immediate vissible in the text.

About the spot for image... You might want rebuild the catalogue after adding new songs. How you cope than with the images? Just keep the initial document, re-build a new one and then copy-past from the new document into the existing one?
Another problem is that such a spot makes two-column usage more dificult to fit (results into huuuuge doc).

Thanks for input and I'm glad it worked for you. I've also submitted this version to www.RioCar.org.

How did you get it to work with XP?

- Jon